Attribute VB_Name = "Module1"
'_______________________________________________________________________________________________________________
'***************************************************************************************************************
'   Module: Module1
'
'_______________________________________________________________________________________________________________
'***************************************************************************************************************

Option Explicit
Option Private Module




''' This variable holds an instance of the event handling class for ThisWorkbook.
Public gclsEventHandler As CEventHandler



''' This variable holds an instance of the event handling class for the specified external workbook.
Public gclsEventHandler2 As CEventHandler2



Public Sub Auto_Open()
    ''' Instantiate the event handling class for ThisWorkbook.
    Set gclsEventHandler = New CEventHandler
End Sub


Public Sub Auto_Close()
    ''' Destroy the event handling class for ThisWorkbook.
    Set gclsEventHandler = Nothing
End Sub


''' This procedure traps the events of the specified external Excel workbook.
''' NOTE: This procedure implicitly assumes the selected workbook is not already open.
Public Sub TrapOutsideWorkbookEvents()

    Dim wkbEventSource As Excel.Workbook
    Dim szFullName As String
    
    On Error GoTo ErrorHandler
    
    szFullName = CStr(Application.GetOpenFilename("Excel Files (*.xls),*.xls", , "Select an Excel File"))
    
    ''' Continue if the user did not cancel the dialog.
    If szFullName <> CStr(False) Then
        ''' Open the specified workbook.
        Application.EnableEvents = False
        Set wkbEventSource = Application.Workbooks.Open(szFullName)
        ''' Create a new instance of the event handler and assign the specified workbook to it.
        Set gclsEventHandler2 = New CEventHandler2
        Set gclsEventHandler2.Book = wkbEventSource
        ''' Inform the user of success.
        MsgBox "Events for " & wkbEventSource.Name & " are now being trapped.", vbInformation, wkbEventSource.Name
    End If
    
ErrorHandler:
    ''' This section is required to ensure that Application event handling
    ''' is always turned back on in case of error.
    Application.EnableEvents = True
End Sub


''' This procedure stops the program from trapping events for the specified external Excel workbook.
Public Sub CancelOutsideWorkbookEvents()
    ''' Destroy the event handling class for the external workbook.
    Set gclsEventHandler2 = Nothing
End Sub
